Introduction to the opportunity¶
Fredericton is the Capital City of the only Canadian fully-bilingual Province of New Brunswick and is beautifully located on the banks of the Saint John River. While one of the least populated provincial capital cities with a population base of less than 60 thousand residents, it offers a wide spectrum of venues and is a governement, university and cultural hub.
As the city grows and develops, it becomes increasingly important to examine and understand it quantitiatively. The City of Fredericton provides open data for everyone and encourages entrepreneurial use to develop services for the benefit of its ciitzens.
Developers, investors, policy makers and/or city planners have an interest in answering the following questions as the need for additional services and citizen protection:
- What neighbourhoods have the highest crime?
- Is population density correlated to crime level?
- Using Foursquare data, what venues are most common in different locations within the city?
- Does the Knowledge Park really need a coffee shop?
- Does the Open Data project have specific enough or thick enough data to empower decisions to be made or is it too aggregate to provide value in its current detail?
Let's find out.
from PIL import Image
import requests
url = 'http://www.tourismfredericton.ca/sites/default/files/field/image/fredericton.jpg'
im = Image.open(requests.get(url, stream=True).raw)
im
Data¶
To understand and explore we will need the following City of Fredericton Open Data:¶
- Open Data Site: http://data-fredericton.opendata.arcgis.com/
- Fredericton Neighbourhoods: http://data-fredericton.opendata.arcgis.com/datasets/neighbourhoods--quartiers
- Fredericton Crime by Neighbourhood: http://data-fredericton.opendata.arcgis.com/datasets/crime-by-neighbourhood-2017--crime-par-quartier-2017
- Fredericton Census Tract Demographics: http://data-fredericton.opendata.arcgis.com/datasets/census-tract-demographics--donn%C3%A9es-d%C3%A9mographiques-du-secteur-de-recensement
- Fredericton locations of interest: https://github.com/JasonLUrquhart/Applied-Data-Science-Capstone/blob/master/Fredericton%20Locations.xlsx
- Foursquare Developers Access to venue data: https://foursquare.com/
Using this data will allow exploration and examination to answer the questions. The neighbourhood data will enable us to properly group crime by neighbourhood. The Census data will enable us to then compare the population density to examine if areas of highest crime are also most densely populated. Fredericton locations of interest will then allow us to cluster and quantitatively understand the venues most common to that location.
This is going to be interesting...
Methodology¶
All steps are referenced beleow in the Appendix: Analysis section.
The methodology will include:
- Loading each data set
- Examine the crime frequency by neighbourhood
- Study the crime types and then pivot analysis of crime type frequency by neighbourhood
- Understand correlation between crimes and population density
- Perform k-means statisical analysis on venues by locations of interest based on findings from crimes and neighbourhood
- Determine which venues are most common statistically in the region of greatest crime count then in all other locations of interest.
- Determine if an area, such as the Knowledge Park needs a coffee shop.
Loading the data¶
After loading the applicable libraries, the referenced geojson neighbourhood data was loaded from the City of Fredericton Open Data site. This dataset uses block polygon shape coordinates which are better for visualization and comparison. The City also uses Ward data but the Neighbourhood location data is more accurate and includes more details. The same type of dataset was then loaded for the population density from the Stats Canada Census tracts.
The third dataset, an excel file, "Crime by Neighbourhood 2017" downloaded from the City of Fredericton Open Data site is found under the Public Safety domain. This dataset was then uploaded for the analysis. It's interesting to note the details of this dataset are aggregated by neighbourhood. It is not an exhaustive set by not including all crimes (violent offenses) nor specific location data of the crime but is referenced by neighbourhood.
This means we can gain an understanding of the crime volume by type by area but not specific enough to understand the distribution properties. Valuable questions such as, "are these crimes occuring more often in a specific area and at a certain time by a specific demographic of people?" cannot be answered nor explored due to what is reasonably assumed to be personal and private information with associated legal risks.
There is value to the city to explore the detailed crime data using data science to predict frequency, location, timing and conditions to best allocated resources for the benefit of its citizens and it's police force. However, human behaviour is complex requiring thick profile data by individual and the conditions surrounding the event(s). To be sufficient for reliable future prediction it would need to demonstrate validity, currency, reliability and sufficiency.
Exploring the data¶
Exploring the count of crimes by neighbourhood gives us the first glimpse into the distribution.
One note is the possibility neighbourhoods names could change at different times. The crime dataset did not mention which specific neighbourhood naming dataset it was using but we assumed the neighbourhood data provided aligned with the neighbourhoods used in the crime data. It may be beneficial for the City to note and timestamp neighbourhood naming in the future or simply reference with neighbourhood naming file it used for the crime dataset.
An example of data errors: There was an error found in the naming of the neighbourhood "Platt". The neighbourhood data stated "Plat" while the crime data stated "Platt". Given the crime dataset was most simple to manipulate it was modified to "Plat". The true name of the neighbourhood is "Platt".
First Visualization of Crime¶
Once the data was prepared, a choropleth map was created to view the crime count by neighbourhood. As expected the region of greatest crime count was found in the downtown and Platt neighbourhoods.
Examining the crime types enables us to learn the most frequent occuring crimes which we then plot as a bar chart to see most frequenty type.
Theft from motor vehicles is most prevalent in the same area as the most frequent crimes. It's interesting to note this area is mostly residential and most do not have garages. It would be interesting to further examine if surveillance is a deterant for motor vehicle crimes in the downtown core compared to low surveillance in the Platt neighbourhood.
Examining 2nd most common crime given it is specific: theft from vehicles¶
After exploring the pivot table showing Crime_Type by Neighbourhood, we drill into a specific type of crime, theft from vehicles and plot the choropleth map to see which area has the greatest frequency.
Again, the Platt neighbourhood appears as the most frequent.
Is this due to population density?
Introducing the Census data to explore the correlation between crime frequency and population density.¶
Visualising the population density enables us to determine that the Platt neighbourhood has lower correlation to crime frequency than I would have expected.
It would be interesting to further study the Census data and if this captures the population that is renting or more temporary/transient poplution, given the City is a University hub.
Look at specific locations to understand the connection to venues using Foursquare data¶
Loading the "Fredericton Locations" data enables us to perform a statistical analysis on the most common venues by location.
We might wonder if the prevalence of bars and clubs in the downtown region has something to do with the higher crime rate in the near Platt region.
Plotting the latitude and longitude coordinates of the locations of interest onto the crime choropleth map enables us to now study the most common venues by using the Foursquare data.
Analysing each Location¶
Grouping rows by location and the mean of the frequency of occurance of each category we venue categories we study the top five most common venues.
Putting this data into a pandas dataframe we can then determine the most common venues by location and plot onto a map.
Results¶
The analysis enabled us to discover and describe visually and quantitatively:
Neighbourhoods in Fredericton
Crime freqency by neighbourhood
Crime type frequency and statistics. The mean crime count in the City of Fredericton is 22.
Crime type count by neighbourhood. Theft from motor vehicles is most prevalent in the same area as the most frequent crimes. It's interesting to note this area is mostly residential and most do not have garages. It would be interesting to further examine if surveillance is a deterant for motor vehicle crimes in the downtown core compared to low surveillance in the Platt neighbourhood.
4.1 Motor Vehicle crimes less than $5000 analysis by neighbourhood and resulting statistics. The most common crime is Other Theft less than 5k followed by Motor Vehicle Theft less than 5k. There is a mean of 6 motor vehicle thefts less than 5k by neighbourhood in the City.
4.2 That population density and resulting visual correlation is not strongly correlated to crime frequency. Causation for crime is not able to be determined given lack of open data specificity by individual and environment.
4.3 Using k-menas, we were able to determine the top 10 most common venues within a 1 km radius of the centroid of the highest crime neighbourhood. The most common venues in the highest crime neighbourhood are coffee shops followed by Pubs and Bars.
While, it is not valid, consistent, reliable or sufficient to assume a higher concentration of the combination of coffee shops, bars and clubs predicts the amount of crime occurance in the City of Fredericton, this may be a part of the model needed to be able to in the future.
We were able to determine the top 10 most common venues by location of interest.
Statisically, we determined there are no coffee shops within the Knowledge Park clusters.
Discussion and Recommendations¶
The City of Fredericton Open Data enables us to gain an understanding of the crime volume by type by area but not specific enough to understand the distribution properties. Valuable questions such as, "are these crimes occuring more often in a specific area and at a certain time by a specific demographic of people?" cannot be answered nor explored due to what is reasonably assumed to be personal and private information with associated legal risks.
There is value to the city to explore the detailed crime data using data science to predict frequency, location, timing and conditions to best allocated resources for the benefit of its citizens and it's police force. However, human behaviour is complex requiring thick profile data by individual and the conditions surrounding the event(s). To be sufficient for reliable future prediction it would need to demonstrate validity, currency, reliability and sufficiency.
A note of caution is the possibility neighbourhoods names could change. The crime dataset did not mention which specific neighbourhood naming dataset it was using but we assumed the neighbourhood data provided aligned with the neighbourhoods used in the crime data. It may be beneficial for the City to note and timestamp neighbourhood naming in the future or simply reference with neighbourhood naming file it used for the crime dataset.
Errors exist in the current open data. An error was found in the naming of the neighbourhood "Platt". The neighbourhood data stated "Plat" while the crime data stated "Platt". Given the crime dataset was most simple to manipulate it was modified to "Plat". The true name of the neighbourhood is "Platt".
Theft from motor vehicles is most prevalent in the same area as the most frequent crimes. It is interesting to note this area is mostly residential and most do not have garages. It would be interesting to further examine if surveillance is a deterant for motor vehicle crimes in the downtown core compared to low surveillance in the Platt neighbourhood.
It would be interesting to further study the Census data and if this captures the population that is renting or more temporary/transient poplution, given the City is a University hub.
Given the findings of the top 10 most frequent venues by locations of interest, the Knowledge Park does not have Coffee Shops in the top 10 most common venues as determined from the Foursquare dataset. Given this area has the greatest concentration of stores and shops as venues, it would be safe to assume a coffee shop would be beneficial to the business community and the citizens of Fredericton.
Conclusion¶
Using a combination of datasets from the City of Fredericton Open Data project and Foursquare venue data we were able to analyse, discover and describe neighbhourhoods, crime, population density and statistically describe quantitatively venues by locations of interest.
While overall, the City of Fredericton Open Data is interesting, it misses the details required for true valued quantitiatve analysis and predictive analytics which would be most valued by investors and developers to make appropriate investments and to minimize risk.
The Open Data project is a great start and empowers the need for a "Citizens Like Me" model to be developed where citizens of digital Fredericton are able to share their data as they wish for detailed analysis that enables the creation of valued services.
import numpy as np # library to handle data in a vectorized manner
import pandas as pd # library for data analysis
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
import json # library to handle JSON files
!conda install -c conda-forge geopy --yes # uncomment this line if you haven't completed the Foursquare API lab
from geopy.geocoders import Nominatim # convert an address into latitude and longitude values
import requests # library to handle requests
from pandas.io.json import json_normalize # tranform JSON file into a pandas dataframe
# Matplotlib and associated plotting modules
import matplotlib.cm as cm
import matplotlib.colors as colors
# import k-means from clustering stage
from sklearn.cluster import KMeans
# for webscraping import Beautiful Soup
from bs4 import BeautifulSoup
import xml
!conda install -c conda-forge folium=0.5.0 --yes
import folium # map rendering library
print('Libraries imported.')
r = requests.get('https://opendata.arcgis.com/datasets/823d86e17a6d47808c6e4f1c2dd97928_0.geojson')
fredericton_geo = r.json()
neighborhoods_data = fredericton_geo['features']
neighborhoods_data[0]
g = requests.get('https://opendata.arcgis.com/datasets/6179d35eacb144a5b5fdcc869f86dfb5_0.geojson')
demog_geo = g.json()
demog_data = demog_geo['features']
demog_data[0]
crime_df = pd.read_csv("Crime_by_neighbourhood_2017.csv")
crime_df.head()
crime_df.drop(['From_Date', 'To_Date'], axis=1,inplace=True)
What is the crime count by neighbourhood?¶
crime_data = crime_df.groupby(['Neighbourhood']).size().to_frame(name='Count').reset_index()
crime_data
crime_data.describe()
crime_data.rename(index=str, columns={'Neighbourhood':'Neighbourh','Count':'Crime_Count'}, inplace=True)
crime_data
crime_data.rename({'Platt': 'Plat'},inplace=True)
crime_data.rename(index=str, columns={'Neighbourhood':'Neighbourh','Count':'Crime_Count'}, inplace=True)
crime_data
address = 'Fredericton, Canada'
geolocator = Nominatim()
location = geolocator.geocode(address)
latitude = location.latitude
longitude = location.longitude
print('The geograpical coordinate of Fredericton, New Brunswick is {}, {}.'.format(latitude, longitude))
world_geo = r'world_countries.json' # geojson file
fredericton_1_map = folium.Map(location=[45.97, -66.65], width=1000, height=750,zoom_start=12)
fredericton_1_map
fredericton_geo = r.json()
threshold_scale = np.linspace(crime_data['Crime_Count'].min(),crime_data['Crime_Count'].max(), 6,dtype=int)
threshold_scale = threshold_scale.tolist()
threshold_scale[-1] = threshold_scale[-1]+1
fredericton_1_map.choropleth(geo_data=fredericton_geo, data=crime_data,columns=['Neighbourh', 'Crime_Count'],
key_on='feature.properties.Neighbourh', threshold_scale=threshold_scale,fill_color='YlOrRd', fill_opacity=0.7,
line_opacity=0.1, legend_name='Fredericton Neighbourhoods')
fredericton_1_map
Examine Crime Types¶
crimetype_data = crime_df.groupby(['Crime_Type']).size().to_frame(name='Count').reset_index()
crimetype_data
crimetype_data.describe()
crimepivot = crime_df.pivot_table(index='Neighbourhood', columns='Crime_Type', aggfunc=pd.Series.count, fill_value=0)
crimepivot
crimetype_data.plot(x='Crime_Type', y='Count', kind='barh')
Let's examine theft from vehicles¶
mvcrime_df = crime_df.loc[crime_df['Crime_Type'] == 'THEFT FROM MV < $5000']
mvcrime_df
mvcrime_data = mvcrime_df.groupby(['Neighbourhood']).size().to_frame(name='Count').reset_index()
mvcrime_data
mvcrime_data.describe()
mvcrime_data.rename({'Platt': 'Plat'},inplace=True)
mvcrime_data.rename(index=str, columns={'Neighbourhood':'Neighbourh','Count':'MVCrime_Count'}, inplace=True)
mvcrime_data
world_geo = r'world_countries.json' # geojson file
fredericton_c_map = folium.Map(location=[45.91, -66.65], width=1000, height=750,zoom_start=12)
fredericton_c_map
## Motor Vehicle Crime <$5000 Count
fredericton_geo = r.json()
threshold_scale = np.linspace(mvcrime_data['MVCrime_Count'].min(), mvcrime_data['MVCrime_Count'].max(),6,dtype=int)
threshold_scale = threshold_scale.tolist()
threshold_scale[-1] = threshold_scale[-1]+1
fredericton_c_map.choropleth(geo_data=fredericton_geo,data=mvcrime_data,columns=['Neighbourh', 'MVCrime_Count'],key_on='feature.properties.Neighbourh',
threshold_scale=threshold_scale, fill_color='YlOrRd',fill_opacity=0.7,line_opacity=0.1,legend_name='Fredericton Neighbourhoods')
fredericton_c_map
Is it possible the higher rate of crime in the downtown area is due to population density?¶
demog_df = pd.read_csv("Census_Tract_Demographics.csv")
demog_df.head()
# Population Density
world_geo = r'world_countries.json' # geojson file
fredericton_d_map = folium.Map(location=[45.94, -66.63], width=1200, height=750,zoom_start=12)
fredericton_d_map
threshold_scale = np.linspace(demog_df['DBpop2011'].min(),demog_df['DBpop2011'].max(),6,dtype=int)
threshold_scale = threshold_scale.tolist()
threshold_scale[-1] = threshold_scale[-1]+1
fredericton_d_map.choropleth(geo_data=demog_geo,data=demog_df,columns=['OBJECTID','DBpop2011'],key_on='feature.properties.OBJECTID',
threshold_scale=threshold_scale,fill_color='PuBuGn',fill_opacity=0.7, line_opacity=0.1,legend_name='Fredericton Population Density')
fredericton_d_map
Let's look at specific locations in Fredericton¶
pointbook = 'Fredericton Locations.xlsx'
workbook_2 = pd.ExcelFile(pointbook)
print(workbook_2.sheet_names)
location_df = workbook_2.parse('Sheet1')
location_df
Add location markers to map¶
for lat, lng, point in zip(location_df['Latitude'], location_df['Longitude'], location_df['Location']):
label = '{}'.format(point)
label = folium.Popup(label, parse_html=True)
folium.CircleMarker([lat, lng],radium=1,popup=label,color='blue',fill=True,fill_color='#3186cc',fill_opacity=0.7,
parse_html=False).add_to(fredericton_c_map)
fredericton_c_map
CLIENT_ID = 'ZDZQDDOB32ORH4QOA5FW12XS1JAQ3ABKLJ3S4H5JZPMJ33QU'
CLIENT_SECRET = 'WQZEFI2A5QB443AI2L1I4BS32RDCGJ0SY2MMDD4BEDHL0VTU'
VERSION = '20181201'
print('Your credentails:')
print('CLIENT_ID: ' + CLIENT_ID)
print('CLIENT_SECRET:' + CLIENT_SECRET)
Let's take a look at nearby venues¶
def getNearbyVenues(names, latitudes, longitudes, radius=1000, LIMIT=100):
venues_list=[]
for name, lat, lng in zip(names, latitudes, longitudes):
print(name)
# create the API request URL
url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(
CLIENT_ID,
CLIENT_SECRET,
VERSION,
lat,
lng,
radius,
LIMIT)
# make the GET request
results = requests.get(url).json()["response"]['groups'][0]['items']
# return only relevant information for each nearby venue
venues_list.append([(
name,
lat,
lng,
v['venue']['name'],
v['venue']['id'],
v['venue']['location']['lat'],
v['venue']['location']['lng'],
v['venue']['categories'][0]['name']) for v in results])
nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
nearby_venues.columns = ['Location',
'Location Latitude',
'Location Longitude',
'Venue',
'Venue id',
'Venue Latitude',
'Venue Longitude',
'Venue Category'
]
return(nearby_venues)
fredericton_data_venues = getNearbyVenues(names=location_df['Location'],
latitudes=location_df['Latitude'],
longitudes=location_df['Longitude']
)
print(fredericton_data_venues.shape)
fredericton_data_venues
print('There are {} unique venue categories.'.format(len(fredericton_data_venues['Venue Category'].unique())))
print('There are {} unique venues.'.format(len(fredericton_data_venues['Venue id'].unique())))
univen = fredericton_data_venues.groupby('Location').nunique('Venue Category')
univen
fredericton_data_venues.groupby('Venue Category').nunique()
Analyze each Location¶
# one hot encoding
freddy_onehot = pd.get_dummies(fredericton_data_venues[['Venue Category']], prefix="", prefix_sep="")
# add neighbourhood column back to dataframe
freddy_onehot['Location'] = fredericton_data_venues['Location']
# move neighbourhood column to the first column
fixed_columns = [freddy_onehot.columns[-1]] + list(freddy_onehot.columns[:-1])
freddy_onehot = freddy_onehot[fixed_columns]
freddy_onehot.head()
freddy_onehot.shape
Group rows by location and by the mean of the frequency of occurrence of each category¶
freddy_grouped = freddy_onehot.groupby('Location').mean().reset_index()
freddy_grouped
freddy_grouped.shape
Print each Location with the top 5 most common venues¶
num_top_venues = 5
for hood in freddy_grouped['Location']:
print("----"+hood+"----")
temp = freddy_grouped[freddy_grouped['Location'] == hood].T.reset_index()
temp.columns = ['venue','freq']
temp = temp.iloc[1:]
temp['freq'] = temp['freq'].astype(float)
temp = temp.round({'freq': 2})
print(temp.sort_values('freq', ascending=False).reset_index(drop=True).head(num_top_venues))
print('\n')
Now into a pandas dataframe¶
def return_most_common_venues(row, num_top_venues):
row_categories = row.iloc[1:]
row_categories_sorted = row_categories.sort_values(ascending=False)
return row_categories_sorted.index.values[0:num_top_venues]
num_top_venues = 10
indicators = ['st', 'nd', 'rd']
# create columns according to number of top venues
columns = ['Location']
for ind in np.arange(num_top_venues):
try:
columns.append('{}{} Most Common Venue'.format(ind+1, indicators[ind]))
except:
columns.append('{}th Most Common Venue'.format(ind+1))
# create a new dataframe
location_venues_sorted = pd.DataFrame(columns=columns)
location_venues_sorted['Location'] = freddy_grouped['Location']
for ind in np.arange(freddy_grouped.shape[0]):
location_venues_sorted.iloc[ind, 1:] = return_most_common_venues(freddy_grouped.iloc[ind, :], num_top_venues)
location_venues_sorted
# set number of clusters
kclusters = 5
freddy_grouped_clustering = freddy_grouped.drop('Location', 1)
# run k-means clustering
kmeans = KMeans(n_clusters=kclusters, random_state=0).fit(freddy_grouped_clustering)
# check cluster labels generated for each row in the dataframe
kmeans.labels_=[1, 1, 1, 0, 1, 4, 1, 3, 2, 1]
kmeans.labels_
Now creating a new dataframe including the cluster as well as the top 10 venues for each Location¶
freddy_merged = location_df
# add clustering labels
freddy_merged['Cluster Labels'] = kmeans.labels_
# merge fredericton_grouped with location df to add latitude/longitude for each location
freddy_merged = freddy_merged.join(location_venues_sorted.set_index('Location'), on='Location')
freddy_merged
# create map
map_clusters = folium.Map(location=[latitude, longitude], zoom_start=11)
# set color scheme for the clusters
x = np.arange(kclusters)
ys = [i+x+(i*x)**2 for i in range(kclusters)]
colors_array = cm.rainbow(np.linspace(0, 1, len(ys)))
rainbow = [colors.rgb2hex(i) for i in colors_array]
# add markers to the map
markers_colors = []
for lat, lon, poi, cluster in zip(freddy_merged['Latitude'], freddy_merged['Longitude'], freddy_merged['Location'], freddy_merged['Cluster Labels']):
label = folium.Popup(str(poi) + ' Cluster ' + str(cluster), parse_html=True)
folium.CircleMarker([lat, lon], radius=5,popup=label,color=rainbow[cluster-1],fill=True,fill_color=rainbow[cluster-1],
fill_opacity=0.7).add_to(map_clusters)
map_clusters